# -*- coding: utf-8 -*-
"""
Created on Tue Jan 12 09:39:47 2021

@author: dell
"""

import pandas as pd
from urllib.parse import quote
import requests
import json
import pymysql
from sqlalchemy import create_engine

def amap_lnglat(target):
    url_str="https://restapi.amap.com/v3/config/district?"
    key='7aee39e5e143265d0359077beb64d705'
    keywords=target[0]
    url=url_str+"key="+key+"&keywords="+str(keywords)+"&subdistrict=0&extensions=all"
    res=requests.get(url).json()
    res=res['districts'][0]
    lng,lat=res['center'].split(",")
    return lng,lat,res['polyline']



city_list=pd.read_excel("地理信息清洗20210111.xlsx",sheet_name=2)
for i in range(len(city_list)):
    try:
        #city_list.iloc[i,6],city_list.iloc[i,7],city_list.iloc[i,8]=amap_lnglat(city_list.iloc[i])
        print(city_list.iloc[i])
        lng,lat,polyline=amap_lnglat(city_list.iloc[i])
        print(lng + ", " + lat)
        break
    except Exception as e:
        print(city_list.iloc[i,1],city_list.iloc[i,4],city_list.iloc[i,9])
        print (e)
        break
        
#创建表#
conn=pymysql.connect('172.28.56.90','mysql','egSQ7HhxajHZjvdX','odsdb')
cursor=conn.cursor()
sql="""CREATE TABLE `county_dict_new` (
  `county_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '区县区号',
  `county_name` varchar(20) DEFAULT NULL COMMENT '区县名称，如朝阳区',
  `short_name` varchar(20) DEFAULT NULL COMMENT '短名称，如朝阳',
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市id',
  `city_name` varchar(20) DEFAULT NULL COMMENT '城市短名称，如北京',
  `province` varchar(20) DEFAULT NULL COMMENT '省份，如河北省',
  `lng` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `lat` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `boundary` text COMMENT '区县坐标边界',
  `state` int(11) DEFAULT NULL COMMENT '状态',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `product` varchar(20) DEFAULT NULL COMMENT '哪些产品在该区县上线',
  PRIMARY KEY (`county_id`)
) ENGINE=InnoDB AUTO_INCREMENT=610125 DEFAULT CHARSET=utf8mb4;"""
#cursor.execute(sql)


#插入数据#
engine=create_engine('mysql+pymysql://mysql:egSQ7HhxajHZjvdX@172.28.56.90:3306/odsdb?charset=utf8')
conn=engine.connect()
#city_list.to_sql('county_dict_new',engine,if_exists='append',index = False,index_label = False)
